﻿using MySql.Data.MySqlClient;
using QLInternet.Connection;
using QLInternet.Model;
using QLInternet.ViewModel;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;

namespace QLInternet.Source
{
    class BaoBieu
    {
        public ObservableCollection<CustomerViewModel> loadDSKHTreThanhToan(Connect conn)
        {
            ObservableCollection<CustomerViewModel> customer = new ObservableCollection<CustomerViewModel>();
            MySqlDataReader reader = null;
            int i = 0;
            String query = "select hoten, cmnd, diachi, dienthoai, tongtien from Khachhang " + 
            "inner join thanhtoan on khachhang.makhachhang = thanhtoan.makhachhang where tinhtrang = 0";
            if (!conn.IsOpenning)
                conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {

                    customer.Add(new CustomerViewModel
                    {
                        STT = (i + 1).ToString(),
                        HoTen = reader.GetString(0),
                        CMND = reader.GetInt32(1),
                        DiaChi = reader.GetString(2),
                        DienThoai = reader.GetInt32(3),
                        TongTienThanhToan = reader.GetInt32(4)
                    });
                    i++;
                }
                
            }
            catch
            {
                MessageBox.Show("Có lỗi trong quá trình truy xuất dữ liệu", "Lỗi");
            }
            finally
            {
                reader.Close();
            }
            return customer;
        }

        public ObservableCollection<CustomerViewModel> loadDSKHMoi(Connect conn)
        {
            int m = DateTime.Now.Month;
            ObservableCollection<CustomerViewModel> customer = new ObservableCollection<CustomerViewModel>();
            MySqlDataReader reader = null;
            int i = 0;
            String query = "select hoten, cmnd, diachi, tengoicuoc, dangky.trongoi,ngaydangky from Khachhang " +
            "inner join dangky on khachhang.makhachhang = dangky.makhachhang " +
            " inner join goicuoc on goicuoc.magoicuoc = dangky.magoicuoc" +
            " where month(ngaydangky) = " + m;
            if (!conn.IsOpenning)
                conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            try
            {
                reader = cmd.ExecuteReader();

            }
            catch
            {
                MessageBox.Show("Có lỗi trong quá trình truy xuất dữ liệu", "Lỗi");
            }
            while (reader.Read())
            {
                string pt = "";
                if (reader.GetInt32(4) == 1)//tron goi
                    pt = "Trọn gói";
                else
                    pt = "Lưu lượng";
                i++;
                customer.Add(new CustomerViewModel
                {
                    STT = (i).ToString(),
                    HoTen = reader.GetString(0),
                    CMND = reader.GetInt32(1),
                    DiaChi = reader.GetString(2),
                    TenGoiCuoc = reader.GetString(3),
                    LoaiPhuongThucDangKy = pt,
                    NgayDangKy = reader.GetDateTime(5).ToString("dd-MM-yyyy hh:mm:ss tt")
                });
            }

            reader.Close();

            return customer;
        }

        public ObservableCollection<DoanhThu> loadDoanhThu(Connect conn, int m, int y)
        {

            ObservableCollection<DoanhThu> li = new ObservableCollection<DoanhThu>();
            MySqlDataReader reader = null;
            int i = 0;
            String query = "select tengoicuoc, dangky.trongoi, dangky.magoicuoc, tinhtrangthanhtoan, count(*),"+
                " sum(goicuoc.trongoi) from" + 
            " dangky inner join goicuoc on dangky.magoicuoc = goicuoc.magoicuoc " + 
            " where month(ngaydangky)=" + m + " and year(ngaydangky)=" + y+
            " group by tengoicuoc, dangky.trongoi";
            if (!conn.IsOpenning)
                conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            try
            {
                reader = cmd.ExecuteReader();

            }
            catch
            {
                MessageBox.Show("Có lỗi trong quá trình truy xuất dữ liệu", "Lỗi");
            }
            while (reader.Read())
            {
                string pt = "";
                if (reader.GetInt32(1) == 1)//tron goi
                    pt = "Trọn gói";
                else
                    pt = "Lưu lượng";

                long tongTien = 0;
                if (reader.GetInt32(1)==1)
                {
                    tongTien = reader.GetInt32(5);
                }
                else
                {
                    tongTien = 0;
                }
                i++;
                li.Add(new DoanhThu
                {
                    STT = i,
                    TenGoiCuoc = reader.GetString(0),
                    LoaiPhuongThuc = pt,
                    SoKhachHangSuDung = reader.GetInt32(4),
                    TongTien = tongTien,

                });
            }

            reader.Close();
            return li;
        }





    }
}
